﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SQLite;
using System.Data.SqlClient;
using System.Data;
using CommonLib;

namespace SQLServer2Sqlite
{
    class Program
    {
        static void Main(string[] args)
        {
            SqlServerToSqlite();
        }

        static void SqlServerToSqlite()
        {
            SqlConnection sqlConn = new SqlConnection();
            string connectString = @"Data Source=gene-f\bandik;database=CrawlPages;integrated security=SSPI;connection timeout=360000";
            sqlConn.ConnectionString = connectString;
            
            sqlConn.Open();
            string selectStringT = "select url from webpages where url like '%{0}%'";


            foreach (string s in GlobalData.Domains)
            {
                SqlCommand sqlcom = new SqlCommand();
                if (sqlConn.State != ConnectionState.Open)
                {
                    sqlConn.Open();
                }
                sqlcom.Connection = sqlConn;
                sqlcom.CommandTimeout = 360000;
                sqlcom.CommandType = CommandType.Text;
                sqlcom.CommandText = string.Format(selectStringT,s);
                SqlDataReader sdr  = sqlcom.ExecuteReader();
                ProcessOneDomainDataCache(s, sdr);
                sqlConn.Close();
            }

        }

        static void ProcessOneDomainData(string domain, SqlDataReader sdr)
        {
            string datasource = domain+".db";
            SQLiteHelper.connectionString = "Data Source="+datasource;
            SQLiteConnection conn = new SQLiteConnection();
            SQLiteConnectionStringBuilder connstr = new SQLiteConnectionStringBuilder();
            connstr.DataSource = datasource;
            conn.ConnectionString = connstr.ToString();
            conn.Open();

            SQLiteCommand cmd = new SQLiteCommand();
            cmd.CommandText ="create table if not exists WebPages (url PRIMARY KEY, ip, contentType , content blob, updateTime);";
            cmd.Connection = conn;
            cmd.ExecuteNonQuery();

            string insertSql = "insert into WebPages values ('{0}', '{1}', '{2}' ,@cachedContent,'{3}');";
            SQLiteCommand insertCommand = new SQLiteCommand();
            insertCommand.Connection = conn;
            insertCommand.Parameters.Add("@cachedContent",DbType.String);
            
            string url = string.Empty;
            string ip = string.Empty;
            string contentType = string.Empty;
            byte[] buf = new byte[1024 * 1024 * 10];
            long len = 0;
            string updateTime = string.Empty;

            while(sdr.Read())
            {
                url = sdr.GetString(0);
                if (url.Length > 200) continue;
                if (SQLiteHelper.Exists(string.Format("Select count(*) from webpages where url ='{0}'", url))) continue;
                ip =sdr.IsDBNull(1)? string.Empty:  sdr.GetString(1);
                contentType =sdr.IsDBNull(2)? string.Empty :  sdr.GetString(2);
                len = sdr.GetBytes(3, 0, buf, 0, 10 * 1024 * 1024);
                updateTime = sdr.GetDateTime(4).ToString("yyyy-MM-dd");
                insertCommand.CommandText = string.Format(insertSql,url,ip,contentType,updateTime);
                insertCommand.Parameters["@cachedContent"].Value =Convert.ToBase64String(buf, 0, (int)len);
                int i = insertCommand.ExecuteNonQuery();
                if (i != 1)
                {

                }

            }
            sdr.Close();




        }

        static void ProcessOneDomainDataCache(string domain, SqlDataReader sdr)
        {
            string datasource = domain + ".db";
            SQLiteHelper.connectionString = "Data Source=" + datasource;
            SQLiteConnection conn = new SQLiteConnection();
            SQLiteConnectionStringBuilder connstr = new SQLiteConnectionStringBuilder();
            connstr.DataSource = datasource;
            conn.ConnectionString = connstr.ToString();
            conn.Open();

            SQLiteCommand cmd = new SQLiteCommand();
            cmd.CommandText = "create table if not exists URLCache (url PRIMARY KEY);";
            cmd.Connection = conn;
            cmd.ExecuteNonQuery();

            string insertSql = "insert into URLCache values ('{0}');";
            SQLiteCommand insertCommand = new SQLiteCommand();
            insertCommand.Connection = conn;

            string url = string.Empty;

            while (sdr.Read())
            {
                url = sdr.GetString(0);
                if (url.Length > 200) continue;
                //if (SQLiteHelper.Exists(string.Format("Select count(*) from URLCache where url ='{0}'", url))) continue;
                insertCommand.CommandText = string.Format(insertSql, url);
                int i = insertCommand.ExecuteNonQuery();
                if (i != 1)
                {

                }

            }
            sdr.Close();




        }

        static string TryGetString(object o)
        {
            string result = string.Empty;
            try
            {
                result = (string)o;
            }
            catch (Exception e)
            {
                ;
            }
            return result;
        }
    }
}
